package org.sageras.util.ExcelUtil;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

/**
 * The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
 * 
 * @author sageeras.wang
 * @version 1.0, Created at 2013年9月14日
 */
public class ExcelUtil {

	/**
	 * 用来验证excel与Vo中的类型是否一致 <br>
	 * Map<栏位类型,只能是哪些Cell类型>
	 */
	private static Map<Class<?>, Integer[]> validateMap = new HashMap<Class<?>, Integer[]>();

	static {
		validateMap
				.put(String[].class, new Integer[] { Cell.CELL_TYPE_STRING });
		validateMap.put(Double[].class,
				new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(String.class, new Integer[] { Cell.CELL_TYPE_STRING });
		validateMap.put(Double.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(Date.class, new Integer[] { Cell.CELL_TYPE_NUMERIC,
				Cell.CELL_TYPE_STRING });
		validateMap
				.put(Integer.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(Float.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(Long.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap
				.put(Boolean.class, new Integer[] { Cell.CELL_TYPE_BOOLEAN });
	}

	/**
	 * 获取cell类型的文字描述
	 * 
	 * @param cellType
	 *            <pre>
	 * Cell.CELL_TYPE_BLANK
	 * Cell.CELL_TYPE_BOOLEAN
	 * Cell.CELL_TYPE_ERROR
	 * Cell.CELL_TYPE_FORMULA
	 * Cell.CELL_TYPE_NUMERIC
	 * Cell.CELL_TYPE_STRING
	 * </pre>
	 * @return
	 */
	private static String getCellTypeByInt(int cellType) {
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK:
			return "Null type";
		case Cell.CELL_TYPE_BOOLEAN:
			return "Boolean type";
		case Cell.CELL_TYPE_ERROR:
			return "Error type";
		case Cell.CELL_TYPE_FORMULA:
			return "Formula type";
		case Cell.CELL_TYPE_NUMERIC:
			return "Numeric type";
		case Cell.CELL_TYPE_STRING:
			return "String type";
		default:
			return "Unknown type";
		}
	}

	/**
	 * 获取单元格值
	 * 
	 * @param cell
	 * @return
	 */
	private static Object getCellValue(Cell cell) {
		if (cell == null
				|| (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils
						.isBlank(cell.getStringCellValue()))) {
			return null;
		}
		int cellType = cell.getCellType();
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK:
			return null;
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getRichStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			return cell.getNumericCellValue();
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		default:
			return null;
		}
	}

	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于单个个sheet
	 * 
	 * @param <T>
	 * 
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *            javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 */
	// public static <T> void exportExcel(String[] headers, Collection<T>
	// dataset,
	// OutputStream out) {
	// exportExcel(headers, dataset, out, null);
	// }

	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于单个个sheet
	 * 
	 * @param <T>
	 * 
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *            javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws IOException
	 */
	public static <T> void exportExcel(String title, Collection<T> dataset,
			OutputStream out, Class<T> clazz) throws IllegalArgumentException,
			IllegalAccessException, IOException {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		write2Sheet(title, sheet, dataset, clazz);
		workbook.write(out);

	}

	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于多个sheet
	 * 
	 * @param <T>
	 * @param sheets
	 *            {@link ExcelSheet}的集合
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @throws IOException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	public static <T> void exportExcel(List<ExcelSheet<T>> sheets,
			OutputStream out, Class<T> clazz, boolean isManySheet)
			throws IllegalArgumentException, IllegalAccessException,
			IOException {
		exportExcel(sheets, out, clazz);
	}

	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于多个sheet
	 * 
	 * @param <T>
	 * @param sheets
	 *            {@link ExcelSheet}的集合
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws IOException
	 */
	public static <T> void exportExcel(List<ExcelSheet<T>> sheets,
			OutputStream out, Class<T> clazz) throws IllegalArgumentException,
			IllegalAccessException, IOException {
		if (CollectionUtils.isEmpty(sheets)) {
			return;
		}
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		for (ExcelSheet<T> sheet : sheets) {
			// 生成一个表格
			HSSFSheet hssfSheet = workbook.createSheet(sheet.getSheetName());
			write2Sheet(sheet.getTitle(), hssfSheet, sheet.getDataset(), clazz);
		}
		workbook.write(out);
	}

	/**
	 * 每个sheet的写入
	 * 
	 * @param sheet
	 *            页签
	 * @param headers
	 *            表头
	 * @param dataset
	 *            数据集合
	 * @param pattern
	 *            日期格式
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	private static <T> void write2Sheet(String title, HSSFSheet sheet,
			Collection<T> dataset, Class<T> clazz)
			throws IllegalArgumentException, IllegalAccessException {
		Map<String, Map<String, String>>map=new HashMap<String, Map<String,String>>();
		List<FieldForSortting> fields = sortFieldByAnno(clazz);
		// 产生表格标题行
		sheet.setDefaultRowHeightInPoints(20);
		sheet.setDefaultColumnWidth(15);
		sheet.createRow(0).createCell(0).setCellValue(title);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fields.size() - 1));
		HSSFRow row = sheet.createRow(1);
		for (FieldForSortting ffs : fields) {
			Field field = ffs.getField();
			field.setAccessible(true);
			ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
			HSSFCell cell = row.createCell(annoCell.index() - 1);
			System.out.println(annoCell.index() - 1);
			HSSFRichTextString text = new HSSFRichTextString(
					annoCell.exportName());
			cell.setCellValue(text);
		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 1;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			for (int i = 0; i < fields.size(); i++) {
				Field field = fields.get(i).getField();
				ExcelCell anno = field.getAnnotation(ExcelCell.class);
				HSSFCell cell = row.createCell(anno.index() - 1);
				field.setAccessible(true);
				Object value = field.get(t);
				String textValue = null;
				if (value instanceof Integer) {
					int intValue = (Integer) value;
					cell.setCellValue(intValue);
				} else if (value instanceof Float) {
					float fValue = (Float) value;
					cell.setCellValue(fValue);
				} else if (value instanceof Double) {
					double dValue = (Double) value;
					cell.setCellValue(dValue);
				} else if (value instanceof Long) {
					long longValue = (Long) value;
					cell.setCellValue(longValue);
				} else if (value instanceof Boolean) {
					boolean bValue = (Boolean) value;
					cell.setCellValue(bValue);
				} else if (value instanceof Date) {

					Date date = (Date) value;
					SimpleDateFormat sdf = new SimpleDateFormat(anno.pattern());
					textValue = sdf.format(date);
				} else {
					// 其它数据类型都当作字符串简单处理
					String empty = StringUtils.EMPTY;
					if (anno != null) {
						empty = anno.defaultValue();
					}
					textValue = value == null ? empty : value.toString();
				}
				if (textValue != null) {
					HSSFRichTextString richString = new HSSFRichTextString(
							textValue);
					cell.setCellValue(richString);
				}
			}
		}

	}

	/**
	 * 把Excel的数据封装成voList
	 * 
	 * @param clazz
	 *            vo的Class
	 * @param inputStream
	 *            excel输入流
	 * @param pattern
	 *            如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
	 * @param logs
	 *            错误log集合
	 * @param arrayCount
	 *            如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
	 * @return voList
	 * @throws IOException
	 * @throws RuntimeException
	 */
	public static <T> Collection<T> importExcel(Class<T> clazz,
			InputStream inputStream, ExcelLogs logs, Integer hasDataIndex)
			throws IOException {
		HSSFWorkbook workBook = new HSSFWorkbook(inputStream);
		List<T> list = new ArrayList<T>();
		List<ExcelLog> logList = new ArrayList<ExcelLog>();
		for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
			HSSFSheet sheet = workBook.getSheetAt(i);
			Iterator<Row> rowIterator = sheet.rowIterator();
			try {
				while (rowIterator.hasNext()) {
					Row row = rowIterator.next();
					if (row.getRowNum() < hasDataIndex) {
						continue;
					}
					// 整行都空，就跳过
					boolean allRowIsNull = true;
					Iterator<Cell> cellIterator = row.cellIterator();
					while (cellIterator.hasNext()) {
						Object cellValue = getCellValue(cellIterator.next());
						if (cellValue != null) {
							allRowIsNull = false;
							break;
						}
					}
					if (allRowIsNull) {
						System.out.println("Excel row " + row.getRowNum()
								+ " all row value is null!");
						continue;
					}
					T t = clazz.newInstance();
					List<FieldForSortting> fields = sortFieldByAnno(clazz);
					StringBuilder log = new StringBuilder();
					for (FieldForSortting ffs : fields) {
						Field field = ffs.getField();
						field.setAccessible(true);
						Cell cell = row.getCell(ffs.getIndex() - 1);
						String errMsg = validateCell(cell, field,
								ffs.getIndex() - 1);
						if (StringUtils.isBlank(errMsg)) {
							Object value = null;
							// 处理特殊情况,Excel中的String,转换成Bean的Date
							if (field.getType().equals(Date.class)
									&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
								Object strDate = getCellValue(cell);
								try {
									value = new SimpleDateFormat(
											ffs.getPattern()).parse(strDate
											.toString());
								} catch (ParseException e) {

									errMsg = MessageFormat
											.format("the cell [{0}] can not be converted to a date ",
													CellReference
															.convertNumToColString(cell
																	.getColumnIndex()));
								}
							} else if (field.getType().equals(Integer.class)) {
								value = new BigDecimal(
										Double.valueOf(getCellValue(cell)
												.toString())).intValue();
							} else {
								value = getCellValue(cell);
								// 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
								ExcelCell annoCell = field
										.getAnnotation(ExcelCell.class);
								if (value instanceof String
										&& !field.getType()
												.equals(String.class)
										&& StringUtils.isNotBlank(annoCell
												.defaultValue())) {
									value = annoCell.defaultValue();
								}
							}
							field.set(t, value);
						}
						if (StringUtils.isNotBlank(errMsg)) {
							log.append(errMsg);
							log.append(";");
							logs.setHasError(true);
						}

					}

					list.add(t);
					logList.add(new ExcelLog(i, t, log.toString(), row
							.getRowNum() + 1));
				}

			} catch (InstantiationException e) {
				throw new RuntimeException(MessageFormat.format(
						"can not instance class:{0}", clazz.getSimpleName()), e);
			} catch (IllegalAccessException e) {
				throw new RuntimeException(MessageFormat.format(
						"can not instance class:{0}", clazz.getSimpleName()), e);
			}
		}
		logs.setLogList(logList);
		return list;
	}

	/**
	 * 驗證Cell類型是否正確 字段是int类型，而数据是double类型时也通过验证
	 * 
	 * @param cell
	 *            cell單元格
	 * @param field
	 *            欄位
	 * @param cellNum
	 *            第幾個欄位,用於errMsg
	 * @return
	 */
	private static String validateCell(Cell cell, Field field, int cellNum) {
		String columnName = CellReference.convertNumToColString(cellNum);
		String result = null;
		Integer[] integers = validateMap.get(field.getType());
		if (integers == null) {
			result = MessageFormat.format("Unsupported type [{0}]", field
					.getType().getSimpleName());
			return result;
		}
		ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
		if (cell == null
				|| (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils
						.isBlank(cell.getStringCellValue()))) {
			if (annoCell != null && annoCell.valid().allowNull() == false) {
				result = MessageFormat.format("the cell [{0}] can not null",
						columnName);
			}
			;
		} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK
				&& annoCell.valid().allowNull()) {
			return result;
		} else {
			List<Integer> cellTypes = Arrays.asList(integers);

			// 如果類型不在指定範圍內,並且沒有默認值
			if (!(cellTypes.contains(cell.getCellType()))
					|| StringUtils.isNotBlank(annoCell.defaultValue())
					&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
				StringBuilder strType = new StringBuilder();
				for (int i = 0; i < cellTypes.size(); i++) {
					Integer intType = cellTypes.get(i);
					strType.append(getCellTypeByInt(intType));
					if (i != cellTypes.size() - 1) {
						strType.append(",");
					}
				}
				result = MessageFormat.format("the cell [{0}] type must [{1}]",
						columnName, strType.toString());
			} else {
				// 类型符合验证,但值不在要求范围内的
				// String in
				if (annoCell.valid().in().length != 0
						&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
					String[] in = annoCell.valid().in();
					String cellValue = cell.getStringCellValue();
					boolean isIn = false;
					for (String str : in) {
						if (str.equals(cellValue)) {
							isIn = true;
						}
					}
					if (!isIn) {
						result = MessageFormat.format(
								"the cell [{0}] value must in {1}", columnName,
								in);
					}
				}
				// 数字型
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					double cellValue = cell.getNumericCellValue();
					// 小于
					if (!Double.isNaN(annoCell.valid().lt())) {
						if (!(cellValue < annoCell.valid().lt())) {
							result = MessageFormat
									.format("the cell [{0}] value must less than [{1}]",
											columnName, annoCell.valid().lt());
						}
					}
					// 大于
					if (!Double.isNaN(annoCell.valid().gt())) {
						if (!(cellValue > annoCell.valid().gt())) {
							result = MessageFormat
									.format("the cell [{0}] value must greater than [{1}]",
											columnName, annoCell.valid().gt());
						}
					}
					// 小于等于
					if (!Double.isNaN(annoCell.valid().le())) {
						if (!(cellValue <= annoCell.valid().le())) {
							result = MessageFormat
									.format("the cell [{0}] value must less than or equal [{1}]",
											columnName, annoCell.valid().le());
						}
					}
					// 大于等于
					if (!Double.isNaN(annoCell.valid().ge())) {
						if (!(cellValue >= annoCell.valid().ge())) {
							result = MessageFormat
									.format("the cell [{0}] value must greater than or equal [{1}]",
											columnName, annoCell.valid().ge());
						}
					}
				}
				// 长度校验
				if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					String cellValue = cell.getStringCellValue();
					if ((annoCell.valid().length() != 0)
							&& (annoCell.valid().length() != cellValue.length())) {
						result = MessageFormat
								.format("the cell [{0}] value length must  equal [{1}]",
										columnName, annoCell.valid().length());
					}
				}
			}
		}
		return result;
	}

	/**
	 * 根据annotation的seq排序后的栏位
	 * 
	 * @param clazz
	 * @return
	 */
	private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
		Field[] superclassfieldsArr =clazz.getSuperclass().getDeclaredFields();
		Field[] fieldsArr = clazz.getDeclaredFields();
		List<FieldForSortting> fields = new ArrayList<FieldForSortting>();
		List<FieldForSortting> annoNullFields = new ArrayList<FieldForSortting>();
		for (Field field : fieldsArr) {
			ExcelCell ec = field.getAnnotation(ExcelCell.class);
			if (ec == null) {
				// 没有ExcelCell Annotation 视为不汇入
				continue;
			}
			fields.add(new FieldForSortting(field, ec.index(), ec.pattern()));
		}
		if (null!=superclassfieldsArr&&superclassfieldsArr.length>0) {
			for (Field field : superclassfieldsArr) {
				ExcelCell ec = field.getAnnotation(ExcelCell.class);
				if (ec == null) {
					// 没有ExcelCell Annotation 视为不汇入
					continue;
				}
				fields.add(new FieldForSortting(field, ec.index(), ec.pattern()));
			}
		}
		
		fields.addAll(annoNullFields);
		sortByProperties(fields, true, false, "index");
		return fields;
	}

	@SuppressWarnings("unchecked")
	private static void sortByProperties(List<? extends Object> list,
			boolean isNullHigh, boolean isReversed, String... props) {
		if (CollectionUtils.isNotEmpty(list)) {
			Comparator<?> typeComp = ComparableComparator.getInstance();
			if (isNullHigh == true) {
				typeComp = ComparatorUtils.nullHighComparator(typeComp);
			} else {
				typeComp = ComparatorUtils.nullLowComparator(typeComp);
			}
			if (isReversed) {
				typeComp = ComparatorUtils.reversedComparator(typeComp);
			}

			List<Object> sortCols = new ArrayList<Object>();

			if (props != null) {
				for (String prop : props) {
					sortCols.add(new BeanComparator(prop, typeComp));
				}
			}
			if (sortCols.size() > 0) {
				Comparator<Object> sortChain = new ComparatorChain(sortCols);
				Collections.sort(list, sortChain);
			}
		}
	}

}
